﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
namespace DSLOrder
{
    /// <summary>
    /// MsSqlDao 的摘要说明
    /// </summary>
    public class MsSqlDao
    {
        public MsSqlDao()
        {
            //
            // TODO: 在此处添加构造函数逻辑
            //
        }
        /// <summary>
        /// 查询数据库中的数据并存入数据表中
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="sql"></param>
        /// <returns>返回一个数据表</returns>
        public DataTable FindAll(SqlConnection conn, string sql)
        {
            SqlCommand cmd = new SqlCommand(sql, conn);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            da.Fill(ds, "bb");
            DataTable dt = ds.Tables["bb"];
            return dt;
        }
        /// <summary>
        /// 将数据表插入到数据库中
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="sql"></param>
        /// <param name="item"></param>
        /// <param name="i"></param>
        /// <returns></returns>
        public int Insert(SqlConnection conn, string sql, DataRow item, int i)
        {
            string p0 = item["商品内码"].ToString();
            string p1 = item["员工编号"].ToString();
            string p2 = item["姓名"].ToString();
            string p3 = item["入司时间"].ToString();
            string p4 = item["部门"].ToString();
            string p5 = item["身份证号码"].ToString();
            string p6 = item["个人绩效"].ToString();
            string p7 = item["提成"].ToString();
            string p8 = item["其他工资"].ToString();
            string p9 = item["水电费"].ToString();
            string p10 = item["加班天数"].ToString();
            string p11 = item["加班时长"].ToString();
            string p12 = item["其它加班"].ToString();
            string p13 = item["迟到"].ToString();
            string p14 = item["早退"].ToString();
            string p15 = item["上班未打卡"].ToString();
            string p16 = item["下班未打卡"].ToString();
            string p17 = item["事假"].ToString();
            string p18 = item["病假"].ToString();
            string p19 = item["年假"].ToString();



            SqlCommand cmd = new SqlCommand(sql, conn);
            SqlParameter[] pm = new SqlParameter[20];

            pm[0] = new SqlParameter("@p0", p0);
            pm[1] = new SqlParameter("@p1", p1);
            pm[2] = new SqlParameter("@p2", p2);
            pm[3] = new SqlParameter("@p3", p3);
            pm[4] = new SqlParameter("@p4", p4);
            pm[5] = new SqlParameter("@p5", p5);
            pm[6] = new SqlParameter("@p6", p6);
            pm[7] = new SqlParameter("@p7", p7);
            pm[8] = new SqlParameter("@p8", p8);
            pm[9] = new SqlParameter("@p9", p9);
            pm[10] = new SqlParameter("@p10", p10);
            pm[11] = new SqlParameter("@p11", p11);
            pm[12] = new SqlParameter("@p12", p12);
            pm[13] = new SqlParameter("@p13", p13);
            pm[14] = new SqlParameter("@p14", p14);
            pm[15] = new SqlParameter("@p15", p15);
            pm[16] = new SqlParameter("@p16", p16);
            pm[17] = new SqlParameter("@p17", p17);
            pm[18] = new SqlParameter("@p18", p18);
            pm[19] = new SqlParameter("@p19", p19);

            foreach (SqlParameter item1 in pm)
            {
                cmd.Parameters.Add(item1);
            }
            i += cmd.ExecuteNonQuery();
            return i;
        }
        public SqlDataReader FindAllReader(string sql, SqlConnection conn)
        {
            SqlCommand cmd = new SqlCommand(sql, conn);
            SqlDataReader dr = cmd.ExecuteReader();
            return dr;
        }
    }
}